﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel;

namespace RESP_IIWaterAssessment
{
    public partial class AVPIncomeFormReport : Form
    {
        public AVPIncomeFormReport()
        {
            InitializeComponent();
        }

        const string categoryName = "IncomeType";
        int regID, avpID, year, month;

        private void AVPIncomeFormReport_Load(object sender, EventArgs e)
        {
            cbRegion.SelectedIndexChanged -= cbRegion_SelectedIndexChanged;
            cbRegion.DataSource = UtilClass.FillCBRegions();
            cbRegion.DisplayMember = "Name";
            cbRegion.ValueMember = "ID";

            if (InformationClass.isAdmin)
            {
                cbRegion.SelectedIndex = -1;
                cbRegion.Text = string.Empty;
                cbRegion.SelectedIndexChanged += new EventHandler(cbRegion_SelectedIndexChanged);
            }
            else
            {
                chbRegion.Checked = true;
                cbRegion.Enabled = chbRegion.Enabled = false;
                regID = InformationClass.regionID;
                cbRegion.SelectedIndexChanged += new EventHandler(cbRegion_SelectedIndexChanged);
                cbRegion.SelectedValue = regID;
                cbRegion_SelectedIndexChanged(cbRegion, null);
                cbAVP.Enabled = true;
                cbAVP.Text = string.Empty;
                //toolStrip1.Visible = false;
            }

            string[] month;
            if (InformationClass.Lang)
                month = new string[] { "Январь", "Февраль", "Март", "Апрель", "Май", "Июнь", "Июль", "Август", "Сентябрь", "Октябрь", "Ноябрь", "Декабрь" };
            else
                month = new string[] { "Январ", "Феврал", "Март", "Апрел", "Май", "Июн", "Июл", "Август", "Сентябр", "Октябр", "Ноябр", "Декабр" };

            for (int i = 1; i <= 12; i++)
            {
                var m = new
                {
                    ID = i,
                    Name = month[i - 1]
                };
                cbMonth.Items.Add(m);
            }
            cbMonth.DisplayMember = "Name";
            cbMonth.ValueMember = "ID";

            for (int i = 1991; i < 2100; i++)
            {
                cbYear.Items.Add(i);
            }
            cbMonth.SelectedIndex = cbMonth.FindStringExact(month[DateTime.Now.Month - 1]);
            cbYear.SelectedIndex = cbYear.FindStringExact(DateTime.Now.Year.ToString());

            cbIncomeType.DataSource = UtilClass.FillCBCategory(categoryName);
            cbIncomeType.DisplayMember = "Name";
            cbIncomeType.ValueMember = "ID";
            cbIncomeType.SelectedIndex = 0;
        }

        private void cbRegion_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbRegion.SelectedIndex >= 0)
            {
                cbAVP.Enabled = true;
                regID = Convert.ToInt32(cbRegion.SelectedValue);
                cbAVP.SelectedIndexChanged -= cbAVP_SelectedIndexChanged;
                cbAVP.DataSource = UtilClass.FillAVP(regID);
                cbAVP.ValueMember = "ID";
                cbAVP.DisplayMember = "Name";
                cbAVP.SelectedIndex = -1;
                cbAVP.Text = string.Empty;
                cbAVP.SelectedIndexChanged += new EventHandler(cbAVP_SelectedIndexChanged);
            }
        }

        private void cbAVP_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbAVP.SelectedIndex >= 0)
            {
                avpID = Convert.ToInt32(cbAVP.SelectedValue);
            }
        }

        private void chbRegion_CheckedChanged(object sender, EventArgs e)
        {
            cbRegion.Enabled = chbRegion.Checked;
        }

        private void chbAVP_CheckedChanged(object sender, EventArgs e)
        {
            cbAVP.Enabled = chbAVP.Checked;
        }

        //private void chbYear_CheckedChanged(object sender, EventArgs e)
        //{
        //    cbYear.Enabled = chbYear.Checked;
        //    if (!chbYear.Checked)
        //        chbMonth.Checked = cbMonth.Enabled = false;
        //}

        private void chbMonth_CheckedChanged(object sender, EventArgs e)
        {
            //if (chbYear.Checked)
            cbMonth.Enabled = chbMonth.Checked;
            //else
            //    chbMonth.Checked = false;
        }

        private void chbIncomeType_CheckedChanged(object sender, EventArgs e)
        {
            cbIncomeType.Enabled = chbIncomeType.Checked;
        }

        private void buttonShow_Click(object sender, EventArgs e)
        {
            dgvAVPIncome.DataSource = null;
            dgvAVPIncome.Columns.Clear();

            SqlConnection con = new SqlConnection(DBClass.ConStr);
            SqlCommand com = new SqlCommand("AVPIncomeReport", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.Add("@Lang", SqlDbType.Bit).Value = InformationClass.Lang;
            com.Parameters.Add("@RegionID", SqlDbType.Int).IsNullable = true;
            com.Parameters.Add("@AVPID", SqlDbType.Int).IsNullable = true;
            //com.Parameters.Add("@Year", SqlDbType.Int).IsNullable = true;
            com.Parameters.Add("@IncomeType", SqlDbType.Int).IsNullable = true;
            com.Parameters.Add("@Date", SqlDbType.SmallDateTime).IsNullable = true;

            if (chbRegion.Checked && regID > 0)
                com.Parameters["@RegionID"].Value = regID;
            else
                com.Parameters["@RegionID"].Value = DBNull.Value;

            if (chbAVP.Checked && avpID > 0)
                com.Parameters["@AVPID"].Value = avpID;
            else
                com.Parameters["@AVPID"].Value = DBNull.Value;

            int param = Convert.ToInt32(cbIncomeType.SelectedValue);

            if (chbIncomeType.Checked && param > 0)
                com.Parameters["@IncomeType"].Value = param;
            else
                com.Parameters["@IncomeType"].Value = DBNull.Value;


            //if (chbYear.Checked && year > 0)
            //    com.Parameters["@Year"].Value = year;
            //else
            //    com.Parameters["@Year"].Value = DBNull.Value;

            if (chbMonth.Checked && month > 0 && year > 0)
            {
                DateTime dt = new DateTime(year, month, 1);
                com.Parameters["@Date"].Value = dt;
            }
            else
                com.Parameters["@Date"].Value = DBNull.Value;

            System.Data.DataTable tab = new System.Data.DataTable("Rep");
            SqlDataAdapter adapter = new SqlDataAdapter(com);

            try
            {
                adapter.Fill(tab);
                dgvAVPIncome.DataSource = tab;
                AddColumns();

                if (InformationClass.Lang)
                {
                    dgvAVPIncome.Columns["Date"].HeaderText = "Месяц";
                    dgvAVPIncome.Columns["RegionName"].HeaderText = "Район";
                    dgvAVPIncome.Columns["AVPName"].HeaderText = "АВП";
                    dgvAVPIncome.Columns["Area"].HeaderText = "Орошаемая площадь, (га)";
                    dgvAVPIncome.Columns["Fact"].HeaderText = "Фактически оказано услуг, (тыс.сум)";
                    dgvAVPIncome.Columns["AVPIncome"].HeaderText = "Всего поступило средств, (тыс.сум)";
                    dgvAVPIncome.Columns["FactGa"].HeaderText = "Фактически оказанно услуг, сум на 1га";
                    dgvAVPIncome.Columns["CollectionGa"].HeaderText = "Собираемость, сум на 1га";
                    dgvAVPIncome.Columns["CollectionPercent"].HeaderText = "Процент собираемости";
                    dgvAVPIncome.Columns["Debet"].HeaderText = "Дебиторская задолженность, тыс.сум";
                    dgvAVPIncome.Columns["Credit"].HeaderText = "Кредиторская задолженность, тыс.сум";

                }
                else
                {
                    dgvAVPIncome.Columns["Date"].HeaderText = "Ой";
                    dgvAVPIncome.Columns["RegionName"].HeaderText = "Туман номи";
                    dgvAVPIncome.Columns["AVPName"].HeaderText = "СИУ номи";
                    dgvAVPIncome.Columns["Area"].HeaderText = "Жами экиладиган майдонлар, га";
                    dgvAVPIncome.Columns["Fact"].HeaderText = "Амалда бажарилган хизматлар, (минг сўм)";
                    dgvAVPIncome.Columns["AVPIncome"].HeaderText = "Жами тушган маблағлар, (минг сўм)";
                    dgvAVPIncome.Columns["FactGa"].HeaderText = "Амалда бажарилган хизматлар (сўм) 1 га учун";
                    dgvAVPIncome.Columns["CollectionGa"].HeaderText = "Собираемость (сўм) 1 га учун";
                    dgvAVPIncome.Columns["CollectionPercent"].HeaderText = "Процент собираемости";
                    dgvAVPIncome.Columns["Debet"].HeaderText = "Дебиторлик қарздорликлари, минг сўм";
                    dgvAVPIncome.Columns["Credit"].HeaderText = "Кредиторлик қарздорликлари, минг сўм";
                }

                for (int i = 0; i < dgvAVPIncome.RowCount; i++)
                {
                    dgvAVPIncome.Rows[i].HeaderCell.Value = (i + 1).ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(msgClass.msgNoData, msgClass.msgAchtung);
                dgvAVPIncome.DataSource = null;
                dgvAVPIncome.Columns.Clear();
            }
        }

        private void cbYear_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbYear.SelectedIndex >= 0)
                year = Convert.ToInt32(cbYear.Text);
        }

        private void cbMonth_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbMonth.SelectedIndex >= 0)
                month = cbMonth.SelectedIndex + 1;
        }

        List<int> percentColIndex;

        int firstInd = 0, countColPerc = 0;
        string[] losses;
        private void AddColumns()
        {
            percentColIndex = new List<int>();
            countColPerc = 0;
            losses = UtilClass.GetCategory(categoryName) as string[];

            if (dgvAVPIncome.ColumnCount > 0)
            {
                firstInd = dgvAVPIncome.ColumnCount;

                for (int i = 0; i < dgvAVPIncome.ColumnCount; i++)
                {
                    for (int j = 0; j < losses.Length; j++)
                    {
                        if (dgvAVPIncome.Columns[i].Name == losses[j])
                        {
                            string name = dgvAVPIncome.Columns[i].Name + "_Percent";
                            dgvAVPIncome.Columns.Add(name, dgvAVPIncome.Columns[i].Name + " %");
                            dgvAVPIncome.Columns[name].DisplayIndex = dgvAVPIncome.Columns[i].DisplayIndex + 1;
                            percentColIndex.Add(dgvAVPIncome.Columns[name].DisplayIndex);
                            countColPerc++;
                            break;
                        }
                    }
                }

                if (dgvAVPIncome.RowCount > 0)
                    AddPercent();
            }
        }

        private void AddPercent()
        {
            for (int row = 0; row < dgvAVPIncome.RowCount; row++)
            {
                for (int i = 0; i < percentColIndex.Count; i++)
                {
                    decimal val1, val2;
                    dgvAVPIncome[firstInd - countColPerc - 5 + i, row].Value.ToString().GetDecimalOrDefault(out val1);
                    dgvAVPIncome["AVPIncome", row].Value.ToString().GetDecimalOrDefault(out val2);

                    dgvAVPIncome[firstInd + i, row].Value = (val1 / val2 * 100).ToString("N2");
                }
            }
        }

        private void buttonExcel_Click(object sender, EventArgs e)
        {
            if (dgvAVPIncome.RowCount > 0)
            {
                MessageBox.Show(msgClass.msgWaiting, msgClass.msgAchtung);
                this.Cursor = Cursors.WaitCursor;
                GetReport();
                excelapp.Visible = true;
                this.Cursor = Cursors.Default;
            }
        }

        private void GetReport()
        {
            excelapp = new Microsoft.Office.Interop.Excel.Application();
            excelapp.SheetsInNewWorkbook = 1;

            if (InformationClass.Lang)
                excelapp.Workbooks.Open(System.Windows.Forms.Application.StartupPath + @"\Templates\i22r18.xls", false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            else
                excelapp.Workbooks.Open(System.Windows.Forms.Application.StartupPath + @"\Templates\i22r18uz.xls", false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            excelappworkbook = excelapp.Workbooks[1];
            excelsheets = excelappworkbook.Worksheets;// .Worksheets[0]; //Sheets 

            int numPage = 1, left = 3, top = 9;

            excelworksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelsheets.get_Item(numPage);

            excelworksheet.Cells.NumberFormat = "@";

            if (chbRegion.Checked)
                excelworksheet.Cells[4, 3] = cbRegion.Text;
            if (chbMonth.Checked)
                excelworksheet.Cells[5, 3] = cbMonth.Text + " " + cbYear.Text;

            for (int i = 0; i < (countColPerc - 1) * 2; i++)
            {
                Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)excelworksheet.Cells[1, 9];
                Microsoft.Office.Interop.Excel.Range column = rng.EntireColumn;
                column.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftToRight, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
            }
            for (int i = 0; i < (countColPerc - 1) * 2; i++)
            {
                if (i % 2 != 0)
                {
                    excelworksheet.get_Range(excelworksheet.Cells[6, i + 9], excelworksheet.Cells[7, i + 8]).Merge(Type.Missing);
                }
            }


            for (int i = 0; i < dgvAVPIncome.Rows.Count; i++)
            {
                for (int j = 2; j < dgvAVPIncome.Columns.Count; j++)
                {
                    excelworksheet.Cells[i + top, dgvAVPIncome.Columns[j].DisplayIndex + left - 2] = dgvAVPIncome[j, i].FormattedValue;
                    addBorder(excelworksheet.Cells[i + top, dgvAVPIncome.Columns[j].DisplayIndex + left - 2]);

                    if (losses.Contains(dgvAVPIncome.Columns[j].Name))
                    {
                        excelworksheet.Cells[6, dgvAVPIncome.Columns[j].DisplayIndex + left - 2] = dgvAVPIncome.Columns[j].Name;
                    }
                }
                excelworksheet.Cells[i + top, 2] = i + 1;
                addBorder(excelworksheet.Cells[i + top, 2]);

            }


            excelworksheet.Columns.AutoFit();
            excelworksheet.Select(numPage);
        }

        Sheets excelsheets;
        Worksheet excelworksheet;
        Microsoft.Office.Interop.Excel.Application excelapp;
        Workbook excelappworkbook;

        private void addBorder(object p)
        {
            Range tmpRange = excelworksheet.get_Range(p, p);
            tmpRange.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
        }

        private void dgvAVPIncome_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            string[] month;
            if (InformationClass.Lang)
                month = new string[] { "Январь", "Февраль", "Март", "Апрель", "Май", "Июнь", "Июль", "Август", "Сентябрь", "Октябрь", "Ноябрь", "Декабрь" };
            else
                month = new string[] { "Январ", "Феврал", "Март", "Апрел", "Май", "Июн", "Июл", "Август", "Сентябр", "Октябр", "Ноябр", "Декабр" };


            if (e.ColumnIndex.Equals(dgvAVPIncome.Columns["Date"].Index) && e.Value != null)
            {
                e.Value = month[((DateTime)e.Value).Month - 1].ToString() + " " + ((DateTime)e.Value).Year.ToString();
            }
        }

        private void buttonPrint_Click(object sender, EventArgs e)
        {
            if (dgvAVPIncome.RowCount > 0)
                PrintDGV.Print_DataGridView(dgvAVPIncome);
        }
    }
}
